# database.py
import sys
import sqlite3
import logging
import os

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

if getattr(sys, 'frozen', False):
    # 打包后，使用 exe 所在目录
    BASE_DIR = os.path.dirname(sys.executable)
else:
    # 脚本运行时，使用当前文件目录
    BASE_DIR = os.path.dirname(os.path.abspath(__file__))

DB_FILE = os.path.join(BASE_DIR, "timer.db")

def get_connection():
    """获取SQLite数据库连接"""
    try:
        return sqlite3.connect(DB_FILE)
    except Exception as e:
        logger.error(f"数据库连接失败: {e}")
        raise

def init_db():
    """初始化数据库表"""
    try:
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS timers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                hours INTEGER NOT NULL,
                minutes INTEGER NOT NULL,
                seconds INTEGER NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.commit()
        logger.info("数据库初始化完成")
    except Exception as e:
        logger.error(f"初始化失败: {e}")
    finally:
        if conn:
            cursor.close()
            conn.close()

def save_timer(hours: int, minutes: int, seconds: int):
    """保存计时记录"""
    try:
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO timers (hours, minutes, seconds)
            VALUES (?, ?, ?)
        """, (hours, minutes, seconds))
        conn.commit()
    except Exception as e:
        logger.error(f"保存失败: {e}")
    finally:
        if conn:
            cursor.close()
            conn.close()

def get_timers() -> list:
    """获取所有计时记录"""
    try:
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, hours, minutes, seconds, 
                datetime(created_at, 'localtime') AS local_time 
            FROM timers 
            ORDER BY created_at DESC
        """)
        return cursor.fetchall()
    except Exception as e:
        logger.error(f"查询失败: {e}")
        return []
    finally:
        if conn:
            cursor.close()
            conn.close()

def clear_timers():
    """清空所有计时记录"""
    try:
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM timers")
        conn.commit()
    except Exception as e:
        logger.error(f"清空失败: {e}")
    finally:
        if conn:
            cursor.close()
            conn.close()

def delete_timer_by_id(timer_id: int):
    """删除指定ID的计时记录"""
    try:
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM timers WHERE id = ?", (timer_id,))
        conn.commit()
    except Exception as e:
        logger.error(f"删除失败: {e}")
        raise
    finally:
        if conn:
            cursor.close()
            conn.close()